package cn.itcast.dao.impl;

import cn.itcast.dao.UserDao;
import cn.itcast.domain.User;
import cn.itcast.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDaoImpl implements UserDao {
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public User findUserByIdAndPassword(int id, String password) {
        String sql="select * from user where id=?and password=?";
        try {
            User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), Integer.toString(id), password);
            return user;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        /*
         * 1定义模板sql*/
        String sql="select count(*) from user where 1 = 1 ";
        StringBuilder sb=new StringBuilder(sql);
        /*
         * 2遍历map,主要是为了动态获取sql*/
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params =new ArrayList<Object>();
        for (String key : keySet) {
            if("currentPage".equals(key)||"rows".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value!=null&&!"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                /*从map中获取值*/
                params.add("%"+value+"%");
            }
        }
        /*这里不要将拼接后的sql忘记
         * */
        sql=sb.toString();
        return template.queryForObject(sql,Integer.class,params.toArray());
    }

    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        String sql="select * from user where 1=1 ";

        StringBuilder sb=new StringBuilder(sql);
        /*
         * 2遍历map,主要是为了动态获取sql*/
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params =new ArrayList<Object>();
        for (String key : keySet) {
            if("currentPage".equals(key)||"rows".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if(value!=null&&!"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                /*从map中获取值*/
                params.add("%"+value+"%");
            }
        }
        /*加分页查询
         * */
        sb.append(" limit ?,? ");
        /*添加分页查询参数
         * */
        params.add(start);
        params.add(rows);
        /*这里不要将拼接后的sql忘记
         * */
        sql=sb.toString();
        return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }

    @Override
    public void updateUser(User user) {
        String sql="update user set username=?,password=?,remark=?,position=? where id=?";
        template.update(sql,user.getUsername(),user.getPassword(),user.getRemark(),user.getPosition(),user.getId());
    }

    @Override
    public List<User> findAll() {
        //1定义sql
        String sql="select *from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));

        return users;
    }

    @Override
    public void addUser(User user) {
        String sql="insert into user values(?,?,?,?,?)";
        template.update(sql,user.getId(),user.getUsername(),user.getPassword(),user.getRemark(),user.getPosition());
    }

    @Override
    public User findUserById(int parseInt) {
        String sql="select *from user where id=?";
        User f = null;
        try {
            f = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), parseInt);
            return f;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public void delUser(int parseInt) {
        String sql="delete  from user where id=?";
        template.update(sql,parseInt);
    }
}
